package Entity;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

import Control.MySQLController;

public class Workshop_BookWorkshopEntity {
	//Attributes
	private Workshop_WorkshopEntity workshop = new Workshop_WorkshopEntity();
	private User needyResident = new User();
	private Date bookingDateTime;
	private Workshop_BookingStatusCode bookingStatusCode = new Workshop_BookingStatusCode();
	
	//Getters and Setters
	public Workshop_WorkshopEntity getWorkshop() {
		return workshop;
	}
	public void setWorkshop(Workshop_WorkshopEntity workshop) {
		this.workshop = workshop;
	}
	public User getNeedyResident() {
		return needyResident;
	}
	public void setNeedyResident(User needyResident) {
		this.needyResident = needyResident;
	}
	public Date getBookingDateTime() {
		return bookingDateTime;
	}
	public void setBookingDateTime(Date bookingDateTime) {
		this.bookingDateTime = bookingDateTime;
	}
	public Workshop_BookingStatusCode getBookingStatusCode() {
		return bookingStatusCode;
	}
	public void setBookingStatusCode(Workshop_BookingStatusCode bookingStatusCode) {
		this.bookingStatusCode = bookingStatusCode;
	}
	
	//Constructors
	public Workshop_BookWorkshopEntity(){}
	
	public Workshop_BookWorkshopEntity(String workshopId, String needyResident, Date bookingDateTime, String bookingStatusCode){
		this.workshop = new Workshop_WorkshopEntity(workshopId);
		this.needyResident = new User(needyResident);
		this.bookingDateTime = bookingDateTime;
		this.bookingStatusCode = new Workshop_BookingStatusCode(bookingStatusCode);
	}
	
	///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
	
	public boolean createBooking(){
		boolean success = false;
		MySQLController db = new MySQLController();
		db.getConnection();
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
		String sql = "INSERT INTO ooadpj.book_workshop(workshop_id, nric, date_time_of_booking, booking_status_code) ";
		sql += "VALUES ('" + workshop.getWorkshopId() + "','" + needyResident.getNric() + "','" + sdf.format(bookingDateTime) + "','" + bookingStatusCode.getBookingStatusCode() + "')";
		if(db.updateRequest(sql)==1)
			success = true;
		db.terminate();
		return success;
	}
	
	public static ArrayList<Workshop_BookWorkshopEntity> retrieveAllBookingsForWorkshop(String workshopId){
		ArrayList<Workshop_BookWorkshopEntity> bookWorkshopList = new ArrayList<Workshop_BookWorkshopEntity>();
		
		ResultSet rs = null;
		MySQLController db = new MySQLController();
		db.getConnection();
		String sql = "SELECT * FROM ooadpj.book_workshop ";
		sql += "INNER JOIN ooadpj.user ON book_workshop.nric = user.nric ";
		sql += "INNER JOIN ooadpj.booking_status ON book_workshop.booking_status_code = booking_status.booking_status_code ";
		sql += "WHERE workshop_id = '" + workshopId + "' ";
		sql += "ORDER BY book_workshop.booking_status_code, name";
		rs = db.readRequest(sql);
		try{
			while(rs.next()){
				Workshop_BookWorkshopEntity workshopBooking = new Workshop_BookWorkshopEntity();
				workshopBooking.needyResident.setNric(rs.getString("nric"));
				workshopBooking.needyResident.setName(rs.getString("user.name"));
				workshopBooking.setBookingDateTime(rs.getDate("date_time_of_booking"));
				workshopBooking.bookingStatusCode.setBookingStatusCode(rs.getString("booking_status_code"));
				workshopBooking.bookingStatusCode.setDescription(rs.getString("booking_status.description"));
				bookWorkshopList.add(workshopBooking);
			}
		}
		catch(Exception e){
			e.printStackTrace();
		}
		finally{
			db.terminate();
		}
		return bookWorkshopList;
	}

	public static ArrayList<Workshop_BookWorkshopEntity> retrieveAllBookingsForResident(String nric){
		ArrayList<Workshop_BookWorkshopEntity> bookWorkshopList = new ArrayList<Workshop_BookWorkshopEntity>();
		
		ResultSet rs = null;
		MySQLController db = new MySQLController();
		db.getConnection();
		String sql = "SELECT * FROM ooadpj.book_workshop ";
		sql += "INNER JOIN ooadpj.workshop ON book_workshop.workshop_id = workshop.workshop_id ";
		sql += "INNER JOIN ooadpj.booking_status ON book_workshop.booking_status_code = booking_status.booking_status_code ";
		sql += "WHERE nric = '" + nric + "' ";
		sql += "ORDER BY book_workshop.booking_status_code, name";
		rs = db.readRequest(sql);
		try{
			while(rs.next()){
				Workshop_BookWorkshopEntity workshopBooking = new Workshop_BookWorkshopEntity();
				workshopBooking.workshop.setWorkshopId(rs.getString("workshop_id"));
				workshopBooking.workshop.setWorkshopName(rs.getString("workshop.name"));
				workshopBooking.setBookingDateTime(rs.getDate("date_time_of_booking"));
				workshopBooking.bookingStatusCode.setBookingStatusCode(rs.getString("booking_status_code"));
				workshopBooking.bookingStatusCode.setDescription(rs.getString("booking_status.description"));
				bookWorkshopList.add(workshopBooking);
			}
		}
		catch(Exception e){
			e.printStackTrace();
		}
		finally{
			db.terminate();
		}
		return bookWorkshopList;
	}
	
	public boolean retrieveABooking(){
		boolean found = false;
		ResultSet rs = null;
		MySQLController db = new MySQLController();
		db.getConnection();
		String sql = "SELECT COUNT(*) AS Application, booking_status_code FROM ooadpj.book_workshop WHERE workshop_id = '" + workshop.getWorkshopId() + "'AND nric = '" + needyResident.getNric() + "'";
		rs = db.readRequest(sql);
		try{
			while(rs.next()){
				if(rs.getInt("Application") == 1){
					found = true;
				}
			}
		}
		catch(Exception e){
			e.printStackTrace();
		}
		finally{
			db.terminate();	
		}
		return found;
	}
	
	public static ArrayList<String> retrieveOBSBookingsCategories(String nric){
		ArrayList<String> bookingIds = new ArrayList<String>();
		ResultSet rs = null;
		MySQLController db = new MySQLController();
		db.getConnection();
		String sql = "SELECT category_code FROM ooadpj.book_workshop INNER JOIN ooadpj.workshop ON book_workshop.workshop_id = workshop.workshop_id WHERE booking_status_code = 'OBS' AND nric = '" + nric + "'";
		rs = db.readRequest(sql);
		try {
			while(rs.next()){
				bookingIds.add(rs.getString("category_code"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally{
			db.terminate();
		}
		return bookingIds;
	}
	
	public static void changeBookingStatus(){
		ArrayList<String> workshopIds = Workshop_WorkshopEntity.retrieveWorkshopsStatusOBS();
		for(int i = 0; i < workshopIds.size(); i++){
			MySQLController db = new MySQLController();
			db.getConnection();
			String sql = "UPDATE ooadpj.book_workshop SET booking_status_code = 'OBS' ";
			sql += "WHERE workshop_id = '" + workshopIds.get(i) + "' AND booking_status_code != 'OBS'";
			db.updateRequest(sql);
			db.terminate();
		}
	}
	
	public boolean deleteBooking(){
		boolean success = false;
		MySQLController db = new MySQLController();
		db.getConnection();
		String sql = "DELETE FROM ooadpj.book_workshop WHERE nric = '" + needyResident.getNric() + "'";
		if(db.updateRequest(sql)==1)
			success = true;
		db.terminate();
		return success;
	}
	
	public boolean deleteBookingForCNL(){
		boolean success = false;
		MySQLController db = new MySQLController();
		db.getConnection();
		String sql = "DELETE FROM ooadpj.book_workshop WHERE workshop_id = '" + workshop.getWorkshopId() + "'";
		if(db.updateRequest(sql)==1)
			success = true;
		db.terminate();
		return success;
	}
}
